const express = require('express');
const { query } = require('../config/database');
// const { authenticateToken } = require('../middleware/auth');

const router = express.Router();

// 获取基金列表（支持高级筛选）
router.get('/', async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 20, 
      search = '', 
      fundType = '',
      category = '',
      riskLevel = '',
      company = '',
      minReturn = '',
      maxReturn = '',
      minAssets = '',
      maxAssets = '',
      sortBy = 'code',
      sortOrder = 'ASC'
    } = req.query;

    // 确保分页参数为整数类型且合法
    const pageNum = Math.max(1, parseInt(page, 10) || 1);
    const limitNum = Math.max(1, parseInt(limit, 10) || 20);
    const offset = Math.max(0, (pageNum - 1) * limitNum);
    
    let whereConditions = ['1=1'];
    let params = [];

    // 搜索条件
    if (search) {
      whereConditions.push('(f.code LIKE ? OR f.name LIKE ? OR f.manager_name LIKE ? OR f.company_name LIKE ? OR f.tags LIKE ?)');
      params.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

    // 基金类型筛选
    if (fundType) {
      const types = fundType.split(',');
      const placeholders = types.map(() => '?').join(',');
      whereConditions.push(`f.fund_type IN (${placeholders})`);
      params.push(...types);
    }

    // 基金分类筛选
    if (category) {
      const categories = category.split(',');
      const placeholders = categories.map(() => '?').join(',');
      whereConditions.push(`f.category IN (${placeholders})`);
      params.push(...categories);
    }

    // 风险等级筛选
    if (riskLevel) {
      const levels = riskLevel.split(',');
      const placeholders = levels.map(() => '?').join(',');
      whereConditions.push(`f.risk_level IN (${placeholders})`);
      params.push(...levels);
    }

    // 基金公司筛选
    if (company) {
      const companies = company.split(',');
      const placeholders = companies.map(() => '?').join(',');
      whereConditions.push(`f.company_name IN (${placeholders})`);
      params.push(...companies);
    }

    // 收益率筛选
    if (minReturn !== '') {
      whereConditions.push('f.yearly_return >= ?');
      params.push(parseFloat(minReturn));
    }

    if (maxReturn !== '') {
      whereConditions.push('f.yearly_return <= ?');
      params.push(parseFloat(maxReturn));
    }

    // 规模筛选
    if (minAssets !== '') {
      whereConditions.push('f.total_assets >= ?');
      params.push(parseFloat(minAssets));
    }

    if (maxAssets !== '') {
      whereConditions.push('f.total_assets <= ?');
      params.push(parseFloat(maxAssets));
    }

    const whereClause = whereConditions.join(' AND ');

    // 打印实际用于 SQL 查询的参数
    console.log('分页参数:', { pageNum, limitNum, offset, params: [...params, limitNum, offset] });

    // 获取总数
    const [countResult] = await query(
      `SELECT COUNT(*) as total FROM funds f WHERE ${whereClause}`,
      params
    );

    // 获取数据
    const funds = await query(
      `SELECT 
        f.id, f.code, f.name, f.company_name, f.manager_name, 
        f.fund_type, f.category, f.risk_level, f.tags, f.nav, f.nav_date,
        f.total_assets, f.establishment_date, f.status,
        f.daily_return, f.weekly_return, f.monthly_return, f.yearly_return,
        f.max_drawdown, f.sharpe_ratio, f.benchmark,
        f.management_fee, f.custodian_fee, f.subscription_fee, f.redemption_fee,
        f.min_investment, f.description
      FROM funds f 
      WHERE ${whereClause}
      ORDER BY f.${sortBy} ${sortOrder}
      LIMIT ${limitNum} OFFSET ${offset}`,
      params
    );

    // 处理标签格式
    const processedFunds = funds.map(fund => ({
      ...fund,
      tags: fund.tags ? JSON.parse(fund.tags) : []
    }));

    res.json({
      data: processedFunds,
      pagination: {
        page: pageNum,
        limit: limitNum,
        total: countResult.total,
        pages: Math.ceil(countResult.total / limitNum)
      }
    });

  } catch (error) {
    console.error('获取基金列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取基金统计信息
router.get('/stats', async (req, res) => {
  try {
    const { search = '', fundType = '', category = '', riskLevel = '', company = '' } = req.query;
    
    let whereConditions = ['1=1'];
    let params = [];

    // 应用筛选条件
    if (search) {
      whereConditions.push('(f.code LIKE ? OR f.name LIKE ? OR f.manager_name LIKE ? OR f.company_name LIKE ? OR f.tags LIKE ?)');
      params.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

    if (fundType) {
      const types = fundType.split(',');
      const placeholders = types.map(() => '?').join(',');
      whereConditions.push(`f.fund_type IN (${placeholders})`);
      params.push(...types);
    }

    if (category) {
      const categories = category.split(',');
      const placeholders = categories.map(() => '?').join(',');
      whereConditions.push(`f.category IN (${placeholders})`);
      params.push(...categories);
    }

    if (riskLevel) {
      const levels = riskLevel.split(',');
      const placeholders = levels.map(() => '?').join(',');
      whereConditions.push(`f.risk_level IN (${placeholders})`);
      params.push(...levels);
    }

    if (company) {
      const companies = company.split(',');
      const placeholders = companies.map(() => '?').join(',');
      whereConditions.push(`f.company_name IN (${placeholders})`);
      params.push(...companies);
    }

    const whereClause = whereConditions.join(' AND ');

    // 获取统计信息
    let statsArr = await query(
      `SELECT 
        COUNT(*) as total_funds,
        AVG(yearly_return) as avg_yearly_return,
        AVG(ABS(max_drawdown)) as avg_max_drawdown,
        AVG(total_assets) as avg_total_assets
      FROM funds f 
      WHERE ${whereClause}`,
      params
    );
    if (!Array.isArray(statsArr)) statsArr = [];
    const [stats = {}] = statsArr;
    // 默认结构，防止 undefined
    res.json({
      totalFunds: stats.total_funds ?? 0,
      avgYearlyReturn: stats.avg_yearly_return?.toFixed(2) ?? '0.00',
      avgMaxDrawdown: stats.avg_max_drawdown?.toFixed(2) ?? '0.00',
      avgTotalAssets: stats.avg_total_assets?.toFixed(2) ?? '0.00'
    });

  } catch (error) {
    console.error('获取基金统计信息错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取基金公司列表
router.get('/companies/list', async (req, res) => {
  try {
    const { search = '' } = req.query;
    
    let whereClause = '1=1';
    let params = [];

    if (search) {
      whereClause = 'name LIKE ?';
      params.push(`%${search}%`);
    }

    const companies = await query(
      `SELECT id, name, code, establishment_date, total_assets, fund_count
       FROM companies 
       WHERE ${whereClause}
       ORDER BY total_assets DESC`,
      params
    ) || [];

    res.json({ data: companies });
  } catch (error) {
    console.error('获取基金公司列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取基金经理列表
router.get('/managers/list', async (req, res) => {
  try {
    const { search = '' } = req.query;
    
    let whereClause = '1=1';
    let params = [];

    if (search) {
      whereClause = 'name LIKE ?';
      params.push(`%${search}%`);
    }

    const managers = await query(
      `SELECT id, name, company_name, experience_years, fund_count, avg_return
       FROM managers 
       WHERE ${whereClause}
       ORDER BY avg_return DESC`,
      params
    ) || [];

    res.json({ data: managers });
  } catch (error) {
    console.error('获取基金经理列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取基金标签
router.get('/tags/list', async (req, res) => {
  try {
    const tags = await query(
      'SELECT DISTINCT tag FROM fund_tags ORDER BY tag'
    ) || [];

    res.json({ data: tags.map(t => t.tag) });
  } catch (error) {
    console.error('获取基金标签错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取筛选选项
router.get('/filter/options', async (req, res) => {
  try {
    // 获取基金类型
    const fundTypes = await query('SELECT DISTINCT fund_type FROM funds WHERE fund_type IS NOT NULL ORDER BY fund_type') || [];
    
    // 获取基金分类
    const categories = await query('SELECT DISTINCT category FROM funds WHERE category IS NOT NULL ORDER BY category') || [];
    
    // 获取风险等级
    const riskLevels = await query('SELECT DISTINCT risk_level FROM funds WHERE risk_level IS NOT NULL ORDER BY risk_level') || [];
    
    // 获取基金公司
    const companies = await query('SELECT DISTINCT company_name FROM funds WHERE company_name IS NOT NULL ORDER BY company_name') || [];

    res.json({
      fundTypes: fundTypes.map(t => t.fund_type),
      categories: categories.map(c => c.category),
      riskLevels: riskLevels.map(r => r.risk_level),
      companies: companies.map(c => c.company_name)
    });

  } catch (error) {
    console.error('获取筛选选项错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 保存基金组合（临时使用默认用户ID）
router.post('/portfolio', async (req, res) => {
  try {
    const { name, description, funds } = req.body;
    const userId = 1; // 临时使用默认用户ID

    const result = await query(
      'INSERT INTO user_portfolios (user_id, name, description, created_at) VALUES (?, ?, ?, NOW())',
      [userId, name, description]
    );

    const portfolioId = result.insertId;

    // 添加基金到组合
    for (const fund of funds) {
      await query(
        'INSERT INTO portfolio_funds (portfolio_id, fund_id, weight) VALUES (?, ?, ?)',
        [portfolioId, fund.id, 1.0 / funds.length] // 默认等权重
      );
    }

    res.status(201).json({
      message: '基金组合保存成功',
      portfolioId
    });

  } catch (error) {
    console.error('保存基金组合错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取用户基金组合（临时使用默认用户ID）
router.get('/portfolio/user', async (req, res) => {
  try {
    const userId = 1; // 临时使用默认用户ID

    const portfolios = await query(
      `SELECT 
        p.id, p.name, p.description, p.created_at,
        COUNT(pf.fund_id) as fund_count,
        SUM(f.total_assets) as total_assets,
        AVG(f.yearly_return) as avg_return
      FROM user_portfolios p
      LEFT JOIN portfolio_funds pf ON p.id = pf.portfolio_id
      LEFT JOIN funds f ON pf.fund_id = f.id
      WHERE p.user_id = ?
      GROUP BY p.id
      ORDER BY p.created_at DESC`,
      [userId]
    ) || [];

    res.json({ data: portfolios });
  } catch (error) {
    console.error('获取用户基金组合错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取组合详情（临时使用默认用户ID）
router.get('/portfolio/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const userId = 1; // 临时使用默认用户ID

    // 验证组合所有权
    const [portfolio] = await query(
      'SELECT * FROM user_portfolios WHERE id = ? AND user_id = ?',
      [id, userId]
    ) || [];

    if (!portfolio) {
      return res.status(404).json({ message: '组合不存在或无权限访问' });
    }

    // 获取组合中的基金
    const funds = await query(
      `SELECT 
        f.*, pf.weight
      FROM portfolio_funds pf
      JOIN funds f ON pf.fund_id = f.id
      WHERE pf.portfolio_id = ?
      ORDER BY pf.weight DESC`,
      [id]
    ) || [];

    // 处理标签格式
    const processedFunds = Array.isArray(funds) ? funds.map(fund => ({
      ...fund,
      tags: fund.tags ? JSON.parse(fund.tags) : []
    })) : [];

    res.json({
      portfolio,
      funds: processedFunds
    });

  } catch (error) {
    console.error('获取组合详情错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除基金组合（临时使用默认用户ID）
router.delete('/portfolio/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const userId = 1; // 临时使用默认用户ID

    // 验证组合所有权
    const [portfolio] = await query(
      'SELECT id FROM user_portfolios WHERE id = ? AND user_id = ?',
      [id, userId]
    ) || [];

    if (!portfolio) {
      return res.status(404).json({ message: '组合不存在或无权限删除' });
    }

    // 删除组合中的基金
    await query('DELETE FROM portfolio_funds WHERE portfolio_id = ?', [id]);
    
    // 删除组合
    await query('DELETE FROM user_portfolios WHERE id = ?', [id]);

    res.json({ message: '组合删除成功' });

  } catch (error) {
    console.error('删除基金组合错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取基金详情
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;

    const [fund] = await query(
      `SELECT 
        f.*, 
        c.name as company_name,
        m.name as manager_name
      FROM funds f
      LEFT JOIN companies c ON f.company_id = c.id
      LEFT JOIN managers m ON f.manager_id = m.id
      WHERE f.id = ?`,
      [id]
    ) || [];

    if (!fund) {
      return res.status(404).json({ message: '基金不存在' });
    }

    // 获取基金净值历史
    const navHistory = await query(
      'SELECT nav_date, nav, cumulative_nav FROM fund_nav WHERE fund_id = ? ORDER BY nav_date DESC LIMIT 30',
      [id]
    ) || [];

    // 获取基金持仓
    const holdings = await query(
      'SELECT * FROM fund_holdings WHERE fund_id = ? ORDER BY weight DESC LIMIT 10',
      [id]
    ) || [];

    // 处理标签格式
    try {
      fund.tags = fund.tags ? JSON.parse(fund.tags) : [];
    } catch (e) {
      fund.tags = [];
    }

    res.json({
      fund,
      navHistory,
      holdings
    });

  } catch (error) {
    console.error('获取基金详情错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取基金画像
router.get('/:id/profile', async (req, res) => {
  try {
    const profileArr = await query('SELECT * FROM fund_profiles WHERE fund_id = ?', [req.params.id]);
    if (!Array.isArray(profileArr) || profileArr.length === 0) {
      return res.status(404).json({ message: '基金不存在' });
    }
    const [profile] = profileArr;
    res.json(profile);
  } catch (error) {
    console.error('获取基金画像错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 新增基金
router.post('/', async (req, res) => {
  try {
    const { name } = req.body;
    if (!name) {
      return res.status(400).json({ message: '缺少参数' });
    }
    const result = await query('INSERT INTO funds (name) VALUES (?)', [name]);
    res.status(201).json({ id: result.insertId });
  } catch (error) {
    console.error('新增基金错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新基金
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name } = req.body;
    if (!name) {
      return res.status(400).json({ message: '缺少参数' });
    }
    const result = await query('UPDATE funds SET name = ? WHERE id = ?', [name, id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '基金不存在' });
    }
    res.json({ message: '更新成功' });
  } catch (error) {
    console.error('更新基金错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除基金
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await query('DELETE FROM funds WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '基金不存在' });
    }
    res.json({ message: '删除成功' });
  } catch (error) {
    console.error('删除基金错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

module.exports = router; 